{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Updated: Using Pandas to Create and Excel Diff\n",
    "\n",
    "The [original article](http://pbpython.com/excel-diff-pandas.html) contains some Updating the Excel diff article to work with more recent versions of pandas that no longer use panel.\n",
    "\n",
    "The new article can be read [here](http://pbpython.com/excel-diff-pandas-update.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the diff function to show the changes in each field\n",
    "def report_diff(x):\n",
    "    return x[0] if x[0] == x[1] else '{} ---> {}'.format(*x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the two files but call the data old and new and create columns to track\n",
    "old = pd.read_excel('sample-address-1.xlsx', 'Sheet1', na_values=['NA'])\n",
    "new = pd.read_excel('sample-address-2.xlsx', 'Sheet1', na_values=['NA'])\n",
    "old['version'] = \"old\"\n",
    "new['version'] = \"new\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>935480</td>\n",
       "      <td>Bruen Group</td>\n",
       "      <td>5131 Nienow Viaduct Apt. 290</td>\n",
       "      <td>Port Arlie</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>14118</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>371770</td>\n",
       "      <td>Cruickshank-Boyer</td>\n",
       "      <td>839 Lana Expressway Suite 234</td>\n",
       "      <td>South Viviana</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>57838</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>548367</td>\n",
       "      <td>Spencer, Grady and Herman</td>\n",
       "      <td>65387 Lang Circle Apt. 516</td>\n",
       "      <td>Greenholtbury</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>58394</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>296620</td>\n",
       "      <td>Schamberger, Hagenes and Brown</td>\n",
       "      <td>26340 Ferry Neck Apt. 612</td>\n",
       "      <td>McCulloughstad</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>74052</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>132971</td>\n",
       "      <td>Williamson, Schumm and Hettinger</td>\n",
       "      <td>89403 Casimer Spring</td>\n",
       "      <td>Jeremieburgh</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>62785</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                              name  \\\n",
       "0          935480                       Bruen Group   \n",
       "1          371770                 Cruickshank-Boyer   \n",
       "2          548367         Spencer, Grady and Herman   \n",
       "3          296620    Schamberger, Hagenes and Brown   \n",
       "4          132971  Williamson, Schumm and Hettinger   \n",
       "\n",
       "                          street            city     state  postal code  \\\n",
       "0   5131 Nienow Viaduct Apt. 290      Port Arlie   Alabama        14118   \n",
       "1  839 Lana Expressway Suite 234   South Viviana   Alabama        57838   \n",
       "2     65387 Lang Circle Apt. 516   Greenholtbury    Alaska        58394   \n",
       "3      26340 Ferry Neck Apt. 612  McCulloughstad    Alaska        74052   \n",
       "4           89403 Casimer Spring    Jeremieburgh  Arkansas        62785   \n",
       "\n",
       "  version  \n",
       "0     old  \n",
       "1     old  \n",
       "2     old  \n",
       "3     old  \n",
       "4     old  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>935480</td>\n",
       "      <td>Bruen Group</td>\n",
       "      <td>5131 Nienow Viaduct Apt. 290</td>\n",
       "      <td>Port Arlie</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>14118</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>371770</td>\n",
       "      <td>Cruickshank-Boyer</td>\n",
       "      <td>839 Lana Expressway Suite 234</td>\n",
       "      <td>South Viviana</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>57838</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>548367</td>\n",
       "      <td>Spencer, Grady and Herman</td>\n",
       "      <td>65387 Lang Circle Apt. 516</td>\n",
       "      <td>Greenholtbury</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>58394</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>132971</td>\n",
       "      <td>Williamson, Schumm and Hettinger</td>\n",
       "      <td>89403 Casimer Spring</td>\n",
       "      <td>Jeremieburgh</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>62785</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>985603</td>\n",
       "      <td>Bosco-Upton</td>\n",
       "      <td>03369 Moe Way</td>\n",
       "      <td>Port Casandra</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>86014</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                              name  \\\n",
       "0          935480                       Bruen Group   \n",
       "1          371770                 Cruickshank-Boyer   \n",
       "2          548367         Spencer, Grady and Herman   \n",
       "3          132971  Williamson, Schumm and Hettinger   \n",
       "4          985603                       Bosco-Upton   \n",
       "\n",
       "                          street           city     state  postal code version  \n",
       "0   5131 Nienow Viaduct Apt. 290     Port Arlie   Alabama        14118     new  \n",
       "1  839 Lana Expressway Suite 234  South Viviana   Alabama        57838     new  \n",
       "2     65387 Lang Circle Apt. 516  Greenholtbury    Alaska        58394     new  \n",
       "3           89403 Casimer Spring   Jeremieburgh  Arkansas        62785     new  \n",
       "4                  03369 Moe Way  Port Casandra  Arkansas        86014     new  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We use the account numbers as the keys to check what is added, dropped and potentially changed\n",
    "# Using sets makes the deduping easy and we can use set operations to figure out groupings\n",
    "old_accts_all = set(old['account number'])\n",
    "new_accts_all = set(new['account number'])\n",
    "\n",
    "dropped_accts = old_accts_all - new_accts_all\n",
    "added_accts = new_accts_all - old_accts_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Join all the data together and ignore indexes so it all gets concatenated\n",
    "all_data = pd.concat([old,new],ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>935480</td>\n",
       "      <td>Bruen Group</td>\n",
       "      <td>5131 Nienow Viaduct Apt. 290</td>\n",
       "      <td>Port Arlie</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>14118</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>371770</td>\n",
       "      <td>Cruickshank-Boyer</td>\n",
       "      <td>839 Lana Expressway Suite 234</td>\n",
       "      <td>South Viviana</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>57838</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>548367</td>\n",
       "      <td>Spencer, Grady and Herman</td>\n",
       "      <td>65387 Lang Circle Apt. 516</td>\n",
       "      <td>Greenholtbury</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>58394</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>296620</td>\n",
       "      <td>Schamberger, Hagenes and Brown</td>\n",
       "      <td>26340 Ferry Neck Apt. 612</td>\n",
       "      <td>McCulloughstad</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>74052</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>132971</td>\n",
       "      <td>Williamson, Schumm and Hettinger</td>\n",
       "      <td>89403 Casimer Spring</td>\n",
       "      <td>Jeremieburgh</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>62785</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                              name  \\\n",
       "0          935480                       Bruen Group   \n",
       "1          371770                 Cruickshank-Boyer   \n",
       "2          548367         Spencer, Grady and Herman   \n",
       "3          296620    Schamberger, Hagenes and Brown   \n",
       "4          132971  Williamson, Schumm and Hettinger   \n",
       "\n",
       "                          street            city     state  postal code  \\\n",
       "0   5131 Nienow Viaduct Apt. 290      Port Arlie   Alabama        14118   \n",
       "1  839 Lana Expressway Suite 234   South Viviana   Alabama        57838   \n",
       "2     65387 Lang Circle Apt. 516   Greenholtbury    Alaska        58394   \n",
       "3      26340 Ferry Neck Apt. 612  McCulloughstad    Alaska        74052   \n",
       "4           89403 Casimer Spring    Jeremieburgh  Arkansas        62785   \n",
       "\n",
       "  version  \n",
       "0     old  \n",
       "1     old  \n",
       "2     old  \n",
       "3     old  \n",
       "4     old  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's see what changes in the main columns we care about\n",
    "# Change drop_duplicates syntax: keep=last\n",
    "changes = all_data.drop_duplicates(subset=[\"account number\", \n",
    "                                           \"name\", \"street\", \n",
    "                                           \"city\",\"state\", \n",
    "                                           \"postal code\"], keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>296620</td>\n",
       "      <td>Schamberger, Hagenes and Brown</td>\n",
       "      <td>26340 Ferry Neck Apt. 612</td>\n",
       "      <td>McCulloughstad</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>74052</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>595932</td>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>4059 Tobias Inlet</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>89271</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>558879</td>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>95616 Enos Grove Suite 139</td>\n",
       "      <td>West Atlas</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>47419</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>880043</td>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>64318</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>935480</td>\n",
       "      <td>Bruen Group</td>\n",
       "      <td>5131 Nienow Viaduct Apt. 290</td>\n",
       "      <td>Port Arlie</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>14118</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     account number                            name  \\\n",
       "3            296620  Schamberger, Hagenes and Brown   \n",
       "24           595932        Kuhic, Eichmann and West   \n",
       "30           558879                   Watsica Group   \n",
       "96           880043                      Beatty Inc   \n",
       "100          935480                     Bruen Group   \n",
       "\n",
       "                           street               city     state  postal code  \\\n",
       "3       26340 Ferry Neck Apt. 612     McCulloughstad    Alaska        74052   \n",
       "24              4059 Tobias Inlet      New Rylanfurt  Illinois        89271   \n",
       "30     95616 Enos Grove Suite 139         West Atlas      Iowa        47419   \n",
       "96   3641 Schaefer Isle Suite 171  North Gardnertown   Wyoming        64318   \n",
       "100  5131 Nienow Viaduct Apt. 290         Port Arlie   Alabama        14118   \n",
       "\n",
       "    version  \n",
       "3       old  \n",
       "24      old  \n",
       "30      old  \n",
       "96      old  \n",
       "100     new  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "changes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all the duplicate rows\n",
    "dupe_accts = changes[changes['account number'].duplicated() == True]['account number'].tolist()\n",
    "dupes = changes[changes[\"account number\"].isin(dupe_accts)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>595932</td>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>4059 Tobias Inlet</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>89271</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>558879</td>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>95616 Enos Grove Suite 139</td>\n",
       "      <td>West Atlas</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>47419</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>880043</td>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>64318</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>595932</td>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>4059 Tobias St</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>89271</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>558879</td>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>829 Big street</td>\n",
       "      <td>Smithtown</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>47919</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>880043</td>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>64918</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     account number                      name                        street  \\\n",
       "24           595932  Kuhic, Eichmann and West             4059 Tobias Inlet   \n",
       "30           558879             Watsica Group    95616 Enos Grove Suite 139   \n",
       "96           880043                Beatty Inc  3641 Schaefer Isle Suite 171   \n",
       "123          595932  Kuhic, Eichmann and West                4059 Tobias St   \n",
       "129          558879             Watsica Group                829 Big street   \n",
       "195          880043                Beatty Inc  3641 Schaefer Isle Suite 171   \n",
       "\n",
       "                  city     state  postal code version  \n",
       "24       New Rylanfurt  Illinois        89271     old  \n",
       "30          West Atlas      Iowa        47419     old  \n",
       "96   North Gardnertown   Wyoming        64318     old  \n",
       "123      New Rylanfurt  Illinois        89271     new  \n",
       "129          Smithtown      Ohio        47919     new  \n",
       "195  North Gardnertown   Wyoming        64918     new  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dupes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pull out the old and new data into separate dataframes\n",
    "change_new = dupes[(dupes[\"version\"] == \"new\")]\n",
    "change_old = dupes[(dupes[\"version\"] == \"old\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the temp columns - we don't need them now\n",
    "change_new = change_new.drop(['version'], axis=1)\n",
    "change_old = change_old.drop(['version'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Index on the account numbers\n",
    "change_new.set_index('account number', inplace=True)\n",
    "change_old.set_index('account number', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_changes = pd.concat([change_old, change_new],\n",
    "                           axis='columns',\n",
    "                           keys=['old', 'new'],\n",
    "                           join='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">old</th>\n",
       "      <th colspan=\"5\" halign=\"left\">new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>account number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>595932</th>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>4059 Tobias Inlet</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>89271</td>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>4059 Tobias St</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>89271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558879</th>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>95616 Enos Grove Suite 139</td>\n",
       "      <td>West Atlas</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>47419</td>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>829 Big street</td>\n",
       "      <td>Smithtown</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>47919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880043</th>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>64318</td>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>64918</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     old                                \\\n",
       "                                    name                        street   \n",
       "account number                                                           \n",
       "595932          Kuhic, Eichmann and West             4059 Tobias Inlet   \n",
       "558879                     Watsica Group    95616 Enos Grove Suite 139   \n",
       "880043                        Beatty Inc  3641 Schaefer Isle Suite 171   \n",
       "\n",
       "                                                         \\\n",
       "                             city     state postal code   \n",
       "account number                                            \n",
       "595932              New Rylanfurt  Illinois       89271   \n",
       "558879                 West Atlas      Iowa       47419   \n",
       "880043          North Gardnertown   Wyoming       64318   \n",
       "\n",
       "                                     new                                \\\n",
       "                                    name                        street   \n",
       "account number                                                           \n",
       "595932          Kuhic, Eichmann and West                4059 Tobias St   \n",
       "558879                     Watsica Group                829 Big street   \n",
       "880043                        Beatty Inc  3641 Schaefer Isle Suite 171   \n",
       "\n",
       "                                                         \n",
       "                             city     state postal code  \n",
       "account number                                           \n",
       "595932              New Rylanfurt  Illinois       89271  \n",
       "558879                  Smithtown      Ohio       47919  \n",
       "880043          North Gardnertown   Wyoming       64918  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all_changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_changes = df_all_changes.swaplevel(axis='columns')[change_new.columns[0:]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">name</th>\n",
       "      <th colspan=\"2\" halign=\"left\">street</th>\n",
       "      <th colspan=\"2\" halign=\"left\">city</th>\n",
       "      <th colspan=\"2\" halign=\"left\">state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">postal code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>old</th>\n",
       "      <th>new</th>\n",
       "      <th>old</th>\n",
       "      <th>new</th>\n",
       "      <th>old</th>\n",
       "      <th>new</th>\n",
       "      <th>old</th>\n",
       "      <th>new</th>\n",
       "      <th>old</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>account number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>595932</th>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>4059 Tobias Inlet</td>\n",
       "      <td>4059 Tobias St</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>89271</td>\n",
       "      <td>89271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558879</th>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>95616 Enos Grove Suite 139</td>\n",
       "      <td>829 Big street</td>\n",
       "      <td>West Atlas</td>\n",
       "      <td>Smithtown</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>47419</td>\n",
       "      <td>47919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880043</th>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>64318</td>\n",
       "      <td>64918</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    name                            \\\n",
       "                                     old                       new   \n",
       "account number                                                       \n",
       "595932          Kuhic, Eichmann and West  Kuhic, Eichmann and West   \n",
       "558879                     Watsica Group             Watsica Group   \n",
       "880043                        Beatty Inc                Beatty Inc   \n",
       "\n",
       "                                      street                                \\\n",
       "                                         old                           new   \n",
       "account number                                                               \n",
       "595932                     4059 Tobias Inlet                4059 Tobias St   \n",
       "558879            95616 Enos Grove Suite 139                829 Big street   \n",
       "880043          3641 Schaefer Isle Suite 171  3641 Schaefer Isle Suite 171   \n",
       "\n",
       "                             city                        state            \\\n",
       "                              old                new       old       new   \n",
       "account number                                                             \n",
       "595932              New Rylanfurt      New Rylanfurt  Illinois  Illinois   \n",
       "558879                 West Atlas          Smithtown      Iowa      Ohio   \n",
       "880043          North Gardnertown  North Gardnertown   Wyoming   Wyoming   \n",
       "\n",
       "               postal code         \n",
       "                       old    new  \n",
       "account number                     \n",
       "595932               89271  89271  \n",
       "558879               47419  47919  \n",
       "880043               64318  64918  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all_changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_changed = df_all_changes.groupby(level=0, axis=1).apply(lambda frame: frame.apply(report_diff, axis=1))\n",
    "df_changed = df_changed.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>city</th>\n",
       "      <th>name</th>\n",
       "      <th>postal code</th>\n",
       "      <th>state</th>\n",
       "      <th>street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>595932</td>\n",
       "      <td>New Rylanfurt</td>\n",
       "      <td>Kuhic, Eichmann and West</td>\n",
       "      <td>89271</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>4059 Tobias Inlet ---&gt; 4059 Tobias St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>558879</td>\n",
       "      <td>West Atlas ---&gt; Smithtown</td>\n",
       "      <td>Watsica Group</td>\n",
       "      <td>47419 ---&gt; 47919</td>\n",
       "      <td>Iowa ---&gt; Ohio</td>\n",
       "      <td>95616 Enos Grove Suite 139 ---&gt; 829 Big street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>880043</td>\n",
       "      <td>North Gardnertown</td>\n",
       "      <td>Beatty Inc</td>\n",
       "      <td>64318 ---&gt; 64918</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>3641 Schaefer Isle Suite 171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                       city                      name  \\\n",
       "0          595932              New Rylanfurt  Kuhic, Eichmann and West   \n",
       "1          558879  West Atlas ---> Smithtown             Watsica Group   \n",
       "2          880043          North Gardnertown                Beatty Inc   \n",
       "\n",
       "        postal code           state  \\\n",
       "0             89271        Illinois   \n",
       "1  47419 ---> 47919  Iowa ---> Ohio   \n",
       "2  64318 ---> 64918         Wyoming   \n",
       "\n",
       "                                           street  \n",
       "0           4059 Tobias Inlet ---> 4059 Tobias St  \n",
       "1  95616 Enos Grove Suite 139 ---> 829 Big street  \n",
       "2                    3641 Schaefer Isle Suite 171  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Diff'ing is done, we need to get a list of removed and added items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>296620</td>\n",
       "      <td>Schamberger, Hagenes and Brown</td>\n",
       "      <td>26340 Ferry Neck Apt. 612</td>\n",
       "      <td>McCulloughstad</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>74052</td>\n",
       "      <td>old</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                            name                     street  \\\n",
       "3          296620  Schamberger, Hagenes and Brown  26340 Ferry Neck Apt. 612   \n",
       "\n",
       "             city   state  postal code version  \n",
       "3  McCulloughstad  Alaska        74052     old  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_removed = changes[changes[\"account number\"].isin(dropped_accts)]\n",
    "df_removed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal code</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>34777</td>\n",
       "      <td>MyCo</td>\n",
       "      <td>7833 Old Pine Drive</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>Florida</td>\n",
       "      <td>32789</td>\n",
       "      <td>new</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     account number  name               street     city    state  postal code  \\\n",
       "199           34777  MyCo  7833 Old Pine Drive  Orlando  Florida        32789   \n",
       "\n",
       "    version  \n",
       "199     new  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_added = changes[changes[\"account number\"].isin(added_accts)]\n",
    "df_added"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save the changes to excel but only include the columns we care about\n",
    "output_columns = [\"account number\", \"name\", \"street\", \"city\", \"state\", \"postal code\"]\n",
    "writer = pd.ExcelWriter(\"my-diff.xlsx\")\n",
    "df_changed.to_excel(writer,\"changed\", index=False, columns=output_columns)\n",
    "df_removed.to_excel(writer,\"removed\",index=False, columns=output_columns)\n",
    "df_added.to_excel(writer,\"added\",index=False, columns=output_columns)\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
